package com.cho.core.db.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import sqlanalysisgui.view.model.FileAnalysisInfo;
import sqlanalysisgui.view.model.TableInfo;

import com.cho.core.db.SQLiteConnection;
import com.cho.core.db.util.DBUtils;
import com.cho.core.util.StringUtils;
import com.cho.core.vo.FileVo;
import com.cho.core.vo.ProjectVo;
import com.cho.core.vo.TableVo;

public class TableDao {
	
	private static Logger logger =  Logger.getLogger("TableDao");
	
	public static void deleteProjectInfo(String projectName){
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		conn = SQLiteConnection.getConnection();
		
		try {
			pstmt = conn.prepareStatement("DELETE FROM PROJECT_INFO WHERE PROJECT_NAME = ?");
			
			pstmt.setString(1, projectName);
			
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
	}
	
	public static void insertProjectInfo(ProjectVo projectVo){
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		conn = SQLiteConnection.getConnection();
		
		try {
			String sql = ""
					+ "INSERT INTO PROJECT_INFO "
					+ "            (PROJECT_NAME, "
					+ "             PROJECT_INFO, "
					+ "             PROJECT_PATH, "
					+ "             REG_DATE) "
					+ "VALUES     ( ?, "
					+ "             ?, "
					+ "             ?, "
					+ "             datetime('now', 'localtime')) ";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, projectVo.getProjectName());
			pstmt.setString(2, projectVo.getProjectDescript());
			//pstmt.setString(2, "TESTSE");
			pstmt.setString(3, projectVo.getProjectDirectory());
			
			pstmt.execute();
		} catch (SQLException e) {
			logger.error("error:" +  e);
			e.printStackTrace();
		} finally {
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
	}
	
	public static void deleteFileList(String projectName){
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		conn = SQLiteConnection.getConnection();
		
		try {
			pstmt = conn.prepareStatement("DELETE FROM FILE_LIST WHERE PROJECT_NAME = ?");
			
			pstmt.setString(1, projectName);
			
			pstmt.execute();
			
		} catch (SQLException e) {
			System.out.println(projectName);
			e.printStackTrace();
		} finally {
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
	}
	
	public static void deleteDbTalbeList(String projectName){
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		conn = SQLiteConnection.getConnection();
		
		try {
			pstmt = conn.prepareStatement("DELETE FROM DB_TABLE_INFO WHERE PROJECT_NAME = ?");
			
			pstmt.setString(1, projectName);
			
			pstmt.execute();
			
		} catch (SQLException e) {
			System.out.println(projectName);
			e.printStackTrace();
		} finally {
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
	}
	
	public static void insertFileList(FileVo fileVo){
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		conn = SQLiteConnection.getConnection();
		
		try {
			String sql = ""
					+ "INSERT INTO FILE_LIST "
					+ "            (PROJECT_NAME, "
					+ "             FILE_SEQ, "
					+ "             FILE_NAME, "
					+ "             FILE_PATH,"
					+ "             FILE_TOTAL_LINE"
					+ "				) "
					+ "VALUES      (?, "
					+ "             ?, "
					+ "             ?, "
					+ "             ?, "
					+ "             ?"
					+ ") ";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, fileVo.getProjectName());
			pstmt.setString(2, fileVo.getFileSeq());
			pstmt.setString(3, fileVo.getFileName());
			pstmt.setString(4, fileVo.getFilePath());
			pstmt.setInt(5, fileVo.getFileTotalLine());
			
			pstmt.execute();
		} catch (SQLException e) {
			System.out.println(fileVo.toString());
			e.printStackTrace();
		} finally {
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
	}
	
	public static void deleteTableList(String projectName){
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		conn = SQLiteConnection.getConnection();
		
		try {
			pstmt = conn.prepareStatement("DELETE FROM TABLE_LIST WHERE PROJECT_NAME = ?");
			
			pstmt.setString(1, projectName);
			
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
	}
	
	public static void insertTableList(TableVo tableVo){
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		conn = SQLiteConnection.getConnection();
		
		try {
			String sql = ""
					+ "INSERT INTO TABLE_LIST "
					+ "            (PROJECT_NAME, "
					+ "             FILE_SEQ, "
					+ "             TABLE_SEQ, "
					+ "             TABLE_NAME, "
					+ "             CRUD_TYPE, "
					+ "             DB_LINK, "
					+ "             SOURCE_LINE) "
					+ "VALUES     (?, "
					+ "            ?, "
					+ "            ?, "
					+ "            ?, "
					+ "            ?, "
					+ "            ?, "
					+ "            ? ) ";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, tableVo.getProjectName());
			pstmt.setString(2, tableVo.getFileSeq());
			pstmt.setInt(3, tableVo.getTableSeq());
			pstmt.setString(4, tableVo.getTableName());
			pstmt.setString(5, tableVo.getCrudType());
			pstmt.setString(6, tableVo.getDbLinkName());
			pstmt.setString(7, tableVo.getSourceLine());
			
			pstmt.execute();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
	}
/*	
	public static List<Map<String, String>> selectFileAndTableList(TableVo talbeVo){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Map<String, String>> result = new ArrayList<Map<String,String>>();
		
		conn = SQLiteConnection.getConnection();
		
		try {
			String sql = ""
					+ "SELECT A.FILE_NAME, A.FILE_PATH, B.TABLE_NAME, B.CRUD_TYPE, B.SOURCE_LINE, (SELECT COMMENTS FROM DB_TABLE_INFO SUB_B 	WHERE B.PROJECT_NAME = SUB_B.PROJECT_NAME AND UPPER(B.TABLE_NAME) = UPPER(SUB_B.TABLE_NAME) AND COMMENTS not in( 'null', 'NULL', '' )) COMMENTS "
					+ "FROM FILE_LIST A, TABLE_LIST B                                                                      "
					+ "WHERE A.PROJECT_NAME = ?			                                                                   "
					+ "AND A.PROJECT_NAME = B.PROJECT_NAME                                                                 "
					+ "AND A.FILE_SEQ = B.FILE_SEQ                                                                         "
					+ "ORDER BY A.FILE_PATH                                                                                ";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, talbeVo.getProjectName());
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				DBUtils.resultSetAutoSetting(rs, result);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(rs != null){try{rs.close();}catch(Exception e){}}
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
		
		return result;
	}*/
	
	public static List<TableInfo> selectFileAndTableList(TableVo tableVo){//selectFileAndTableListForGUI
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<TableInfo> result = new ArrayList<TableInfo>();
		
		conn = SQLiteConnection.getConnection();
		
		try {
			String sql = ""
					+ "SELECT A.FILE_NAME, A.FILE_PATH, B.TABLE_NAME || IFNULL((SELECT ' ('|| COMMENTS || ')' FROM DB_TABLE_INFO SUB_B 	WHERE B.PROJECT_NAME = SUB_B.PROJECT_NAME 	AND UPPER(B.TABLE_NAME) = UPPER(SUB_B.TABLE_NAME) AND COMMENTS not in( 'null', 'NULL', '' ) ) ,'') TABLE_NAME, B.CRUD_TYPE, B.SOURCE_LINE, B.FILE_SEQ			 	"
					+ "FROM FILE_LIST A, TABLE_LIST B                                                                      	"
					+ "WHERE A.PROJECT_NAME = ?			                                                                   	"
					+ "$DYNAMIC_SEARCH_CONDISION$			                                                                "
					+ "AND A.PROJECT_NAME = B.PROJECT_NAME                                                                 	"
					+ "AND A.FILE_SEQ = B.FILE_SEQ                                                                         	"
					+ "ORDER BY $DYNAMIC_ORDER_BY$ B.TABLE_NAME, B.CRUD_TYPE, B.SOURCE_LINE		                            ";
			
			sql = DBUtils.tableSearchCondisionForTableVo(sql, tableVo);
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, tableVo.getProjectName());
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				result.add(new TableInfo(rs.getString("TABLE_NAME").replace(" (null)", ""), rs.getString("CRUD_TYPE"), rs.getString("SOURCE_LINE"), rs.getString("FILE_PATH"), rs.getString("FILE_SEQ"), rs.getString("FILE_NAME")));
			}
			
			logger.debug("SQL: " + sql);
			logger.debug("PARAM: " + tableVo.toString());
			logger.debug("result: " + result.size());
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(rs != null){try{rs.close();}catch(Exception e){}}
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
		
		return result;
	}
	
	public static List<TableInfo> selectSourceUseAndDatabaseUseTableForGUI(String projectName){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<TableInfo> result = new ArrayList<TableInfo>();
		
		conn = SQLiteConnection.getConnection();
		
		try {
			String sql = ""
					+   "SELECT TABLE_NAME, COMMENTS     "
					+   "FROM DB_TABLE_INFO              "
					+   "WHERE PROJECT_NAME = ?          "
					+   "AND TABLE_NAME  IN (            "
					+   "	SELECT  TABLE_NAME           "
					+   "	FROM TABLE_LIST              "
					+   "	WHERE PROJECT_NAME = ?       "
					+   ")                               ";
			
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, projectName);
			pstmt.setString(2, projectName);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				result.add(new TableInfo(rs.getString("TABLE_NAME"), rs.getString("COMMENTS")));
			}
			
			logger.debug("SQL: " + sql);
			logger.debug("PARAM: " + projectName);
			logger.debug("result: " + result.size());
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(rs != null){try{rs.close();}catch(Exception e){}}
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
		
		return result;
	}
	
	public static List<TableInfo> selectSourceUseAndDatabaseNotUseTableForGUI(String projectName){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<TableInfo> result = new ArrayList<TableInfo>();
		
		conn = SQLiteConnection.getConnection();
		
		try {
			String sql = ""
					+   "SELECT TABLE_NAME, COMMENTS     "
					+   "FROM DB_TABLE_INFO              "
					+   "WHERE PROJECT_NAME = ?          "
					+   "AND TABLE_NAME NOT IN (            "
					+   "	SELECT  TABLE_NAME           "
					+   "	FROM TABLE_LIST              "
					+   "	WHERE PROJECT_NAME = ?       "
					+   ")                               ";
			
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, projectName);
			pstmt.setString(2, projectName);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				result.add(new TableInfo(rs.getString("TABLE_NAME"), rs.getString("COMMENTS")));
			}
			
			logger.debug("SQL: " + sql);
			logger.debug("PARAM: " + projectName);
			logger.debug("result: " + result.size());
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(rs != null){try{rs.close();}catch(Exception e){}}
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
		
		return result;
	}
	
	public static List<Map<String, String>> selectTableCRUDList(TableVo tableVo){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Map<String, String>> result = new ArrayList<Map<String,String>>();
		
		conn = SQLiteConnection.getConnection();
		
		try {
			String sql = ""
					+ "SELECT A.FILE_NAME,                                                   "
					+ "A.FILE_PATH,                                                          "
					+ "B.TABLE_NAME || IFNULL((SELECT ' ('|| COMMENTS || ')' FROM DB_TABLE_INFO SUB_B 	WHERE B.PROJECT_NAME = SUB_B.PROJECT_NAME 	AND UPPER(B.TABLE_NAME) = UPPER(SUB_B.TABLE_NAME) AND COMMENTS not in( 'null', 'NULL', '' ) ) ,'') TABLE_NAME,		 	"                                                         
					+ "B.CRUD_TYPE,                                                          "
					+ "A.FILE_SEQ,                                                   		 "
					+ "SUM( CASE CRUD_TYPE WHEN 'C' THEN 1 ELSE 0 END) CRUD_TYPE_C,          "
					+ "SUM( CASE CRUD_TYPE WHEN 'R' THEN 1 ELSE 0 END) CRUD_TYPE_R,          "
					+ "SUM( CASE CRUD_TYPE WHEN 'U' THEN 1 ELSE 0 END) CRUD_TYPE_U,          "
					+ "SUM( CASE CRUD_TYPE WHEN 'D' THEN 1 ELSE 0 END) CRUD_TYPE_D           "
					+ "FROM FILE_LIST A, TABLE_LIST B                                        "
					+ "WHERE A.PROJECT_NAME = ?		                                         "
					+ "AND A.PROJECT_NAME = B.PROJECT_NAME                                   "
					+ "AND A.FILE_SEQ = B.FILE_SEQ                                           "
					+ "$DYNAMIC_SEARCH_CONDISION$                                           "
					+ "GROUP BY FILE_NAME, FILE_PATH, TABLE_NAME,B.CRUD_TYPE                 "
					+ "ORDER BY A.FILE_PATH, B.TABLE_NAME,						             "
					+ "CASE B.CRUD_TYPE                                                      "
					+ "WHEN 'C' THEN 1                                                       "
					+ "WHEN 'R' THEN 2                                                       "
					+ "WHEN 'U' THEN 3                                                       "
					+ "WHEN 'D' THEN 4                                                       "
					+ "ELSE 5 END 			                                                 ";
			
			pstmt = conn.prepareStatement(DBUtils.tableSearchCondisionForTableVo(sql, tableVo));
		
			pstmt.setString(1, tableVo.getProjectName());
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				DBUtils.resultSetAutoSetting(rs, result);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(rs != null){try{rs.close();}catch(Exception e){}}
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
		
		return result;
	}
	
	public static List<Map<String, String>> selectFileList(TableVo tableVo){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Map<String, String>> result = new ArrayList<Map<String,String>>();
		
		conn = SQLiteConnection.getConnection();
		
		try {
			String sql = ""
					+ "SELECT A.FILE_NAME,                                           "
					+ "A.FILE_PATH,                                                  "
					+ "A.FILE_SEQ                                                   "
					+ "FROM FILE_LIST A, TABLE_LIST B                                "
					+ "WHERE A.PROJECT_NAME = ?                                      "
					+ "$DYNAMIC_SEARCH_CONDISION$                                      "
					+ "AND A.PROJECT_NAME = B.PROJECT_NAME                           "
					+ "AND A.FILE_SEQ = B.FILE_SEQ                                   "
					+ "GROUP BY FILE_NAME, FILE_PATH						         "
					+ "ORDER BY A.FILE_PATH                                          ";
					
			pstmt = conn.prepareStatement(DBUtils.tableSearchCondisionForTableVo(sql, tableVo));
			
			pstmt.setString(1, tableVo.getProjectName());
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				DBUtils.resultSetAutoSetting(rs, result);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(rs != null){try{rs.close();}catch(Exception e){}}
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
		
		return result;
	}
	
	public static List<Map<String, String>> selectTableList(TableVo tableVo){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Map<String, String>> result = new ArrayList<Map<String,String>>();
		
		conn = SQLiteConnection.getConnection();
		
		try {
			String sql = ""
					+ "SELECT "
					+ "B.TABLE_NAME,         		"
					+ "B.TABLE_NAME || IFNULL((SELECT ' ('|| COMMENTS || ')' FROM DB_TABLE_INFO SUB_B 	WHERE B.PROJECT_NAME = SUB_B.PROJECT_NAME 	AND UPPER(B.TABLE_NAME) = UPPER(SUB_B.TABLE_NAME) AND COMMENTS not in( 'null', 'NULL', '' ) ) ,'') TABLE_NAME_AND_COMMENT		 	"
					+ "FROM TABLE_LIST B           "
					+ "WHERE PROJECT_NAME = ?    "
					+ "$DYNAMIC_SEARCH_CONDISION$     "
					+ "GROUP BY TABLE_NAME       "
					+ "ORDER BY TABLE_NAME       ";
					
			pstmt = conn.prepareStatement(DBUtils.tableSearchCondisionForTableVo(sql, tableVo));
			
			pstmt.setString(1, tableVo.getProjectName());
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				DBUtils.resultSetAutoSetting(rs, result);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(rs != null){try{rs.close();}catch(Exception e){}}
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
		
		return result;
	}
	
	public static List<String> selectTableNameList(String projectName){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<String> result = new ArrayList<String>();
		
		conn = SQLiteConnection.getConnection();
		
		try {
			String sql = ""
					+ "SELECT "
					+ "B.TABLE_NAME         		"
					+ "FROM TABLE_LIST B           "
					+ "WHERE PROJECT_NAME = ?    "
					+ "GROUP BY TABLE_NAME       "
					+ "ORDER BY TABLE_NAME       ";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, projectName);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				result.add(rs.getString(1));
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(rs != null){try{rs.close();}catch(Exception e){}}
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
		
		return result;
	}
	
	public static List<Map<String, String>> selectProjectList(){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Map<String, String>> result = new ArrayList<Map<String,String>>();
		
		conn = SQLiteConnection.getConnection();
		
		try {
			String sql = ""
					+ "SELECT *         "
					+ "FROM PROJECT_INFO           "
					+ "ORDER BY REG_DATE DESC      ";
			
			pstmt = conn.prepareStatement(sql);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				DBUtils.resultSetAutoSetting(rs, result);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(rs != null){try{rs.close();}catch(Exception e){}}
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
		
		return result;
	}
	
	public static List<FileAnalysisInfo> selectProjectFileAnalysisInfo(String projectName){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<FileAnalysisInfo> list = new ArrayList<FileAnalysisInfo>(); 
		FileAnalysisInfo result = new FileAnalysisInfo();
		
		conn = SQLiteConnection.getConnection();
		
		try {
			String sql = ""
					+ "SELECT                                                                                  "
					+ " (SELECT COUNT(*) FROM FILE_LIST WHERE PROJECT_NAME = ?) TOTAL_FILE,                    "
					+ " (SELECT SUM(FILE_TOTAL_LINE) FROM FILE_LIST WHERE PROJECT_NAME = ?) TOTAL_FILE_LINE,   "
					+ " COUNT(DISTINCT FILE_SEQ) USE_SQL_FILE,                                                 "
					+ " COUNT( CASE WHEN CRUD_TYPE = 'C' THEN 1 ELSE NULL END) C_TYPE,                         "
					+ " COUNT( CASE WHEN CRUD_TYPE = 'R' THEN 1 ELSE NULL END) R_TYPE,                         "
					+ " COUNT( CASE WHEN CRUD_TYPE = 'U' THEN 1 ELSE NULL END) U_TYPE,                         "
					+ " COUNT( CASE WHEN CRUD_TYPE = 'D' THEN 1 ELSE NULL END) D_TYPE,                          "
					+ " ROUND(CAST((CAST(COUNT( CASE WHEN CRUD_TYPE = 'C' THEN 1 ELSE NULL END) AS REAL) / COUNT(*)) AS REAL) * 100, 1) C_TYPE_RATIO,"                       
					+ " ROUND(CAST((CAST(COUNT( CASE WHEN CRUD_TYPE = 'R' THEN 1 ELSE NULL END) AS REAL) / COUNT(*)) AS REAL) * 100, 1) R_TYPE_RATIO,"                       
					+ " ROUND(CAST((CAST(COUNT( CASE WHEN CRUD_TYPE = 'U' THEN 1 ELSE NULL END) AS REAL) / COUNT(*)) AS REAL) * 100, 1) U_TYPE_RATIO,"                       
					+ " ROUND(CAST((CAST(COUNT( CASE WHEN CRUD_TYPE = 'D' THEN 1 ELSE NULL END) AS REAL) / COUNT(*)) AS REAL) * 100, 1) D_TYPE_RATIO," 
					+ " COUNT(*) CRUD_TOTAL_COUNT   					                                                                             "
					+ "FROM TABLE_LIST                                                                         "
					+ "WHERE PROJECT_NAME = ?                                                                  ";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, projectName);
			pstmt.setString(2, projectName);
			pstmt.setString(3, projectName);
			
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				result = new FileAnalysisInfo(
						rs.getString("TOTAL_FILE"),
						rs.getString("TOTAL_FILE_LINE"),
						rs.getString("USE_SQL_FILE"),
						rs.getString("C_TYPE"),
						rs.getString("R_TYPE"),
						rs.getString("U_TYPE"),
						rs.getString("D_TYPE"),
						rs.getString("C_TYPE_RATIO"),
						rs.getString("R_TYPE_RATIO"),
						rs.getString("U_TYPE_RATIO"),
						rs.getString("D_TYPE_RATIO")
						);
				list.add(result);
			}
			
			if(logger.isDebugEnabled()){
				logger.debug("SQL:" + sql);
				logger.debug("projectName:" + projectName);
				logger.debug("result:" + result.toString());
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(rs != null){try{rs.close();}catch(Exception e){}}
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
		
		return list;
	}
	
	public static List<FileAnalysisInfo> selectProjectTableUseRatioAnalysisList(String projectName){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<FileAnalysisInfo> list = new ArrayList<FileAnalysisInfo>(); 
		FileAnalysisInfo result = new FileAnalysisInfo();
		
		conn = SQLiteConnection.getConnection();
		
		try {
			String sql = ""
					+ "SELECT * FROM (                      																														"                                      
					+ "SELECT                      																														"                                      
					+ " TABLE_NAME,                                                                                                                                     "
					+ " COUNT(*) TOTAL,                                                                                                                                 "
					+ " ROUND(CAST(COUNT(*) AS REAL) / CAST( (SELECT COUNT(*) FROM TABLE_LIST WHERE PROJECT_NAME = ?) AS REAL) * 100, 1) TABLE_RATIO,        			"
					+ " COUNT( CASE WHEN CRUD_TYPE = 'C' THEN 1 ELSE NULL END) C_TYPE,                                                                                  "
					+ " COUNT( CASE WHEN CRUD_TYPE = 'R' THEN 1 ELSE NULL END) R_TYPE,                                                                                  "
					+ " COUNT( CASE WHEN CRUD_TYPE = 'U' THEN 1 ELSE NULL END) U_TYPE,                                                                                  "
					+ " COUNT( CASE WHEN CRUD_TYPE = 'D' THEN 1 ELSE NULL END) D_TYPE                                                                                   "
					+ "FROM TABLE_LIST                                                                                                                                  "
					+ "WHERE PROJECT_NAME = ?                                                                                                                           "
					+ "GROUP BY TABLE_NAME                                                                                                                              "
					+ "ORDER BY 2 DESC                                                                                                                              "
					+ ")                                                                                                                               "
					+ "UNION ALL                                                                                                                                        "
					+ "SELECT                                                                                                                                           "
					+ " '합계',                                                                                                                                         "
					+ " COUNT(*) TOTAL,                                                                                                                                 "
					+ " 100.0,                                                                                                                                          "
					+ " COUNT( CASE WHEN CRUD_TYPE = 'C' THEN 1 ELSE NULL END) C_TYPE,                                                                                  "
					+ " COUNT( CASE WHEN CRUD_TYPE = 'R' THEN 1 ELSE NULL END) R_TYPE,                                                                                  "
					+ " COUNT( CASE WHEN CRUD_TYPE = 'U' THEN 1 ELSE NULL END) U_TYPE,                                                                                  "
					+ " COUNT( CASE WHEN CRUD_TYPE = 'D' THEN 1 ELSE NULL END) D_TYPE                                                                                   "
					+ "FROM TABLE_LIST                                                                                                                                  "
					+ "WHERE PROJECT_NAME = ?                                                                                                                           ";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, projectName);
			pstmt.setString(2, projectName);
			pstmt.setString(3, projectName);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				result = new FileAnalysisInfo(
						rs.getString("TABLE_NAME"),
						rs.getString("TOTAL"),
						rs.getString("C_TYPE"),
						rs.getString("R_TYPE"),
						rs.getString("U_TYPE"),
						rs.getString("D_TYPE"),
						rs.getString("TABLE_RATIO"),
						"hook"
						);
				list.add(result);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(rs != null){try{rs.close();}catch(Exception e){}}
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
		
		return list;
	}
	
	public static List<FileAnalysisInfo> selectProjectTableUseTopTenAnalysisList(String projectName){
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<FileAnalysisInfo> list = new ArrayList<FileAnalysisInfo>(); 
		FileAnalysisInfo result = new FileAnalysisInfo();
		
		conn = SQLiteConnection.getConnection();
		
		try {
			String sql = ""
				+ "	SELECT                         "
				+ "	 TABLE_NAME,                   "
				+ "	 TABLE_TOTAL                   "
				+ "	FROM(                          "
				+ "		SELECT                     "                                       
				+ "		 TABLE_NAME,               "                                       
				+ "		 COUNT(*) TABLE_TOTAL      "
				+ "		FROM TABLE_LIST            "
				+ "		WHERE PROJECT_NAME = ?     "                                         
				+ "		GROUP BY TABLE_NAME        "
				+ "		ORDER BY 2 DESC            "
				+ "	)A                             "
				+ "	LIMIT 10 OFFSET 0              ";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, projectName);
			
			rs = pstmt.executeQuery();
			
			while(rs.next()){
				result = new FileAnalysisInfo(
						rs.getString("TABLE_NAME"),
						rs.getString("TABLE_TOTAL")
						);
				list.add(result);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(rs != null){try{rs.close();}catch(Exception e){}}
			if(pstmt != null){try{pstmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
		
		return list;
	}
	
	public static void insertDbTableInfo(ProjectVo vo){
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs = null;
		
		//오라클 테이블 정보 가져오기
		List<Map<String, String>> tableList = DictionaryDao.selectDbAllTables(vo);
		
		conn = SQLiteConnection.getConnection();
		
		try {
			boolean newQuery = true;
			String query = "";
			int taleSize = tableList.size();
			int i = 0;
			stmt =  conn.createStatement();
			
			for (i = 0; i < taleSize; i++) {
			    if (newQuery) {
			        query = "INSERT INTO DB_TABLE_INFO (PROJECT_NAME, TABLE_NAME, COMMENTS)";
			        newQuery = false;
			    }
			    else {
			        query += " UNION";
			    }
			    
			    
			    query += " SELECT '" + vo.getProjectName() + "', '"+tableList.get(i).get("TABLE_NAME")+"', '"+tableList.get(i).get("COMMENTS")+"'";
			 
			    if (i!=0 && i%499==0) {
			    	stmt.execute(query);
			        newQuery = true;
			    }
			}
			 
			logger.debug("sql:" + query);
			
			//executing remaining lines
			if (i%499!=0) {
				stmt.execute(query);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if(rs != null){try{rs.close();}catch(Exception e){}}
			if(stmt != null){try{stmt.close();}catch(Exception e){}}
			if(conn != null){try{conn.close();}catch(Exception e){}}
		}
	}
	
	
	public static void main(String[] args) {
		//TableDao t = new TableDao();
		//t.selectFileAndTableList("TEST1");
		
	}
	
}
